// 导入数据库操作模块
const { default: axios } = require('axios')
const db = require('../db/index')
const jwt = require('jsonwebtoken')
const config = require('../config')
const md5 = require('md5')
const { adminLoginLimiter, getLostLimiter } = require('../middleware/rateLimiter')


// 发布的操作函数
exports.publish = (req, res) => {
    console.log('/publish')
    // 获取请求体
    const info = req.body
    console.log('req.body', req.body)
    // 定义sql语句
    const sqlStr = 'INSERT INTO loseschema SET ?'
    db.query(sqlStr, info, (err, result) => {
        if (err) {
            console.log(err)
            return res.send(err)
        }
        res.send('插入成功')
        console.log('插入成功')
    })
}

// 获取数据的操作函数
exports.getLost = (req, res) => {
    // 应用限流中间件
    getLostLimiter(req, res, () => {
        let { type, page } = req.query

        let sqlStr
        let queryParams = []
        let countSql = ""
        let isPageQuery = false
        const pageSize = 10

        if (type) {
            sqlStr = `SELECT * FROM loseschema WHERE type=?`
            countSql = `SELECT COUNT(*) as total FROM loseschema WHERE type=?`
            queryParams = [type]
        } else {
            sqlStr = `SELECT * FROM loseschema`
            countSql = `SELECT COUNT(*) as total FROM loseschema`
        }

        // 如果提供了页码，添加分页限制
        if (page) {
            isPageQuery = true
            // 计算偏移量（告诉数据库要跳过多少条记录）
            const offset = (parseInt(page) - 1) * pageSize
            // 跳过offset条记录，并返回后面pageSize条记录
            // 说明：SELECT * FROM loseschema WHERE type=? LIMIT ?, ? type, offset偏移量, pageSize页面大小
            sqlStr += ` LIMIT ?, ?`
            queryParams.push(offset, pageSize)
        }

        // 如果是分页查询，先获取总记录数
        if (isPageQuery) {
            db.query(countSql, type ? [type] : [], (err, countResult) => {
                if (err) {
                    console.log(err)
                    return res.send(err)
                }

                const total = countResult[0].total
                const totalPages = Math.ceil(total / pageSize)

                // 执行分页查询
                db.query(sqlStr, queryParams, (err, result) => {
                    if (err) {
                        console.log(err)
                        return res.send(err)
                    }

                    res.send({
                        data: result,
                        pagination: {
                            total,
                            currentPage: parseInt(page),
                            pageSize,
                            totalPages
                        }
                    })
                })
            })
        } else {
            // 如果不是分页查询，直接返回所有结果
            db.query(sqlStr, queryParams, (err, result) => {
                if (err) {
                    console.log(err)
                    return res.send(err)
                }
                console.log('result', result)
                res.send(result)
            })
        }
    })
}

// 收藏物品
exports.toCollect = (req, res) => {
    let { id, openId } = req.body
    console.log('id', id)
    console.log('openid', openId)
    const sqlStr = 'INSERT INTO user_collection set ?'


    db.query(sqlStr, [{ id, open_id: openId }], (err, result) => {
        if (err) {
            return res.send({
                msg: err,
                err: err
            })
        }
        res.send('收藏成功')
    })

}
// 取消收藏物品

exports.unCollect = (req, res) => {
    let { id, openId } = req.body
    console.log('id', id)
    console.log('openid', openId)
    const sqlStr = 'DELETE FROM user_collection WHERE id = ? AND open_id = ?'

    db.query(sqlStr, [id, openId], (err, result) => {
        if (err) {
            console.log('err', err)
            return res.send({
                msg: 'err',
                err: err
            })
        }
        res.send('取消收藏成功')
    })
}

// 查询是否收藏物品
exports.isCollect = (req, res) => {
    let { id, openId } = req.query
    console.log('id', id)
    console.log('openid', openId)
    const sqlStr = `SELECT * FROM user_collection WHERE open_id = ? AND id = ?`
    db.query(sqlStr, [openId, id], (err, result) => {
        if (err) {
            console.log(err)
            return res.send({
                msg: 'err',
                err: err
            })
        }
        if (result.length > 0) {
            return res.send({ status: 1, msg: '已收藏' })
        }
        res.send({ status: 0, msg: '未收藏' })
    })
}

// 获取收藏夹页面数据
exports.getCollect = (req, res) => {
    console.log('/getCollect')

    // 获取查询参数
    let { openId, type } = req.query
    console.log('type:', type)
    console.log('openId:', openId)

    // 构建 SQL 语句
    let sqlStr = `
        SELECT loseschema.* 
        FROM user_collection 
        INNER JOIN loseschema 
        ON user_collection.id = loseschema.id
    `
    const params = []

    // 动态添加条件
    if (openId || type) {
        sqlStr += ` WHERE `
        if (openId) {
            sqlStr += `user_collection.open_id = ?`
            params.push(openId)
        }
        if (type) {
            if (params.length > 0) sqlStr += ` AND `
            sqlStr += `loseschema.type = ?`
            params.push(type)
        }
    }

    // 执行查询
    db.query(sqlStr, params, (err, result) => {
        if (err) {
            console.error(err)
            return res.send({ status: 0, msg: '查询出错', err })
        }
        if (result.length > 0) {
            return res.send({ status: 1, msg: '数据获取成功', data: result })
        }
        res.send({ status: 0, msg: '未查询到相关数据' })
    })
}


// 获取我的发布页面数据
exports.getMyPublish = (req, res) => {
    console.log('/getMyPublish')
    let { type, openId } = req.query
    // console.log(type)
    const sqlStr = `SELECT * FROM loseschema where type=? and openId =?`
    db.query(sqlStr, [type, openId], (err, result) => {
        if (err) {
            console.log(err)
            return res.send(err)
        }
        res.send(result)
    })
}


// 根据id获取数据的操作函数
exports.getLostById = (req, res) => {
    console.log('/getLostById')
    let { id } = req.query
    // console.log('id', id)
    const sqlStr = `SELECT *FROM loseschema where id=?`
    db.query(sqlStr, id, (err, result) => {
        if (err) {
            res.send({
                msg: 'err',
                err: err
            })
        }
        res.send(result)
    })
}

// 根据二级分类获取数据
exports.getLostBySecond = (req, res) => {
    let { type, classify2 } = req.query
    console.log('/getLostBySecond')
    const sqlStr = `SELECT * FROM loseschema where type=? and classify2 =?`
    console.log('req.query', req.query)

    db.query(sqlStr, [type, classify2], (err, result) => {
        if (err) {
            console.log(err)
            return res.send(err)
        }
        res.send(result)
    })
}

// 模糊查询物品
exports.getLoseByLike = (req, res) => {
    let { name } = req.query
    console.log('/getLoseByLike')
    // 使用 LIKE 关键字进行模糊查询
    const sqlStr = `SELECT * FROM loseschema WHERE name LIKE ?`
    console.log('req.query', req.query)

    // 为 name 参数添加通配符
    const searchName = `%${name}%`

    db.query(sqlStr, [searchName], (err, result) => {
        if (err) {
            console.log(err)
            return res.status(500).send({ error: '数据库查询出错' })
        }
        res.send(result)
    })
}


// 实现登录的操作函数
exports.login = async (req, res) => {
    // appid
    const appid = 'wxbb2b4bc9a72b5cbb'
    // 密钥
    const secret = '282502ea6200d4d4757014b10023cc68'
    const { code } = req.query
    try {
        const result = await axios.get(`https://api.weixin.qq.com/sns/jscode2session?appid=${appid}&secret=${secret}&js_code=${code}&grant_type=authorization_code`)
        // 把openid解构出来
        const { openid } = result.data
        console.log(result)
        res.send(openid)
    } catch (err) {
        console.log(err)
        res.send('err', err)
    }
}

// 添加用户
exports.addUser = async (req, res) => {

    console.log('/addUser')
    // 获取请求体
    let { openId, nickName, avatarUrl, phone } = req.body
    console.log(req.body)

    // 定义sql语句
    const sqlStr = `INSERT INTO user_info(openId,nickname,phone,avatarUrl) VALUES(?,?,?,?)`
    db.query(sqlStr, [openId, nickName, phone, avatarUrl], (err, result) => {
        if (err) {
            console.log(err)
            return res.send(err)
        }
        res.send(result)
    })
}

// 获取用户信息
exports.getUser = (req, res) => {
    // 从请求查询参数中获取openId
    let { openId } = req.query
    console.log('请求参数:', req.query)

    let sqlStr       // SQL查询语句
    let queryParams = [] // 查询参数

    // 如果有openId参数，则按openId查询特定用户
    if (openId) {
        sqlStr = `SELECT * FROM user_info WHERE openId=?`
        queryParams = [openId]
    }
    // 如果没有openId参数，则查询所有用户
    else {
        sqlStr = `SELECT * FROM user_info`
    }

    // 执行数据库查询
    db.query(sqlStr, queryParams, (err, result) => {
        // 错误处理
        if (err) {
            console.log('数据库错误:', err)
            return res.send({
                status: 'error',
                message: '数据库查询失败',
                error: err
            })
        }

        console.log('查询结果:', result)

        // 返回查询结果
        res.send({
            status: 'success',
            data: result
        })
    })
}

// 修改用户信息
exports.updateUser = (req, res) => {
    // 1. 修改手机号
    console.log('/updateUser')
    // 获取请求体
    let { openId, phone, avatarUrl, nickName } = req.body
    console.log('req.body', req.body)

    // 参数校验
    if (!openId || !phone) {
        return res.status(400).send({ message: 'openId和phone不能为空' })
    }

    // 定义更新手机号的SQL语句
    const sqlStr = `UPDATE user_info SET phone = ?,avatarUrl = ?,nickName = ? WHERE openId = ?`

    db.query(sqlStr, [phone, avatarUrl, nickName, openId], (err, result) => {
        if (err) {
            console.log(err)
            return res.status(500).send(err)
        }

        // 检查是否成功更新了记录
        if (result.affectedRows === 0) {
            return res.status(404).send({ message: '未找到匹配的用户记录' })
        }

        res.send({ message: '手机号更新成功', result })
    })
}

// 检查用户是否存在
exports.checkUser = async (req, res) => {
    // 从请求查询参数中获取openId
    let { openId } = req.query
    console.log('检查用户参数:', req.query)

    if (!openId) {
        return res.send({
            status: 0,
            message: '缺少必要参数openId',
            exists: false
        })
    }

    const sqlStr = `SELECT * FROM user_info WHERE openId=?`

    // 执行数据库查询
    db.query(sqlStr, [openId], (err, result) => {
        // 错误处理
        if (err) {
            console.log('数据库错误:', err)
            return res.send({
                status: 0,
                message: '数据库查询失败',
                error: err,
                exists: false
            })
        }

        // 检查查询结果是否存在用户
        const exists = result && result.length > 0

        // 返回检查结果
        res.send({
            status: 1,
            exists: exists,
            message: exists ? '用户已存在' : '用户不存在'
        })
    })
}

// 删除用户
exports.deleteUser = (req, res) => {
    console.log('/deleteUser')
    // 获取请求体
    let { openId } = req.body
    console.log('req.body', req.body)

    // 参数校验
    if (!openId) {
        return res.status(400).send({ message: 'openId不能为空' })
    }

    // 定义删除用户的SQL语句
    const sqlStr = `DELETE FROM user_info WHERE openId = ?`

    db.query(sqlStr, [openId], (err, result) => {
        if (err) {
            console.log(err)
            return res.status(500).send(err)
        }

        // 检查是否成功删除了记录
        if (result.affectedRows === 0) {
            return res.status(404).send({ message: '未找到匹配的用户记录' })
        }

        res.send({
            status: 1,
            message: '用户删除成功',
            result
        })
    })
}

// 后台删除物品
exports.deleteLose = async (req, res) => {

    console.log('/deleteLose')
    // 获取请求体
    let { id } = req.body
    console.log(req.body)

    // 定义sql语句
    const sqlStr = `DELETE FROM loseschema WHERE id = ?`
    db.query(sqlStr, id, (err, result) => {
        if (err) {
            console.log(err)
            return res.send(err)
        }
        res.send(result)
    })
}

// 更改物品信息
exports.updateLose = (req, res) => {
    console.log('/updateLose')
    // 获取请求体
    const info = req.body
    console.log('req.body', req.body)

    // 定义 SQL 语句，根据主键 id 更新其他字段
    // 注意：使用反引号包裹desc字段，因为它是MySQL保留关键字
    const sqlStr = `
    UPDATE \`loseschema\`
    SET 
        \`type\` = ?, 
        \`classify1\` = ?, 
        \`classify2\` = ?, 
        \`name\` = ?, 
        \`date\` = ?, 
        \`region\` = ?, 
        \`phone\` = ?, 
        \`desc\` = ?, 
        \`imgList\` = ?, 
        \`time\` = ?, 
        \`openId\` = ?
    WHERE \`id\` = ?
`


    // 从请求体提取字段数据
    const values = [
        info.type,
        info.classify1,
        info.classify2,
        info.name,
        info.date,
        info.region,
        info.phone,
        info.desc,
        info.imgList,
        info.time,
        info.openId,
        info.id // 主键 id 用于定位更新的记录
    ]

    // 执行 SQL 语句
    db.query(sqlStr, values, (err, result) => {
        if (err) {
            console.error(err)
            return res.status(500).json({
                code: "DB_ERROR",
                message: "数据库操作失败",
                error: err
            })
        }
        if (result.affectedRows === 0) {
            return res.status(404).json({
                code: "NOT_FOUND",
                message: "未找到对应的记录"
            })
        }
        res.json({
            code: "SUCCESS",
            message: "更新成功",
            data: result
        })
        console.log('更新成功')
    })
}

// 管理员相关处理函数
// 添加管理员
exports.addAdmin = (req, res) => {
    const { currentAdminId, currentAdminPrivilege } = req.body
    if (currentAdminPrivilege !== 'super_admin') {
        return res.send({ status: 0, msg: '权限不足，只有超级管理员可以添加管理员' })
    }

    const { account, password, privilege: newPrivilege } = req.body

    // 对密码进行MD5加密
    const encryptedPassword = md5(password)

    const sqlStr = 'INSERT INTO admins SET ?'
    db.query(sqlStr, {
        account,
        password: encryptedPassword,  // 存储加密后的密码
        privilege: newPrivilege
    }, (err, result) => {
        if (err) {
            console.log(err)
            return res.send({ status: 0, msg: '添加失败', err })
        }
        res.send({ status: 1, msg: '添加成功' })
    })
}

// 删除管理员
exports.deleteAdmin = (req, res) => {
    // 从请求体中获取当前管理员信息
    const { currentAdminId, currentAdminPrivilege } = req.body
    if (currentAdminPrivilege !== 'super_admin') {
        return res.send({ status: 0, msg: '权限不足，只有超级管理员可以删除管理员' })
    }

    const { id } = req.body
    // 不允许删除自己
    if (id === currentAdminId) {
        return res.send({ status: 0, msg: '不能删除当前登录的管理员账号' })
    }

    const sqlStr = 'DELETE FROM admins WHERE id = ?'
    db.query(sqlStr, id, (err, result) => {
        if (err) {
            console.log(err)
            return res.send({ status: 0, msg: '删除失败', err })
        }
        res.send({ status: 1, msg: '删除成功' })
    })
}

// 修改管理员信息
exports.updateAdmin = (req, res) => {
    // 从请求体中获取当前管理员信息
    const { currentAdminId, currentAdminPrivilege } = req.body
    if (currentAdminPrivilege !== 'super_admin') {
        return res.send({ status: 0, msg: '权限不足，只有超级管理员可以修改管理员信息' })
    }

    const { id, account, password, privilege: newPrivilege } = req.body

    // 对密码进行MD5加密
    const encryptedPassword = md5(password)

    const sqlStr = 'UPDATE admins SET account = ?, password = ?, privilege = ? WHERE id = ?'
    db.query(sqlStr, [account, encryptedPassword, newPrivilege, id], (err, result) => {
        if (err) {
            console.log(err)
            return res.send({ status: 0, msg: '修改失败', err })
        }
        res.send({ status: 1, msg: '修改成功' })
    })
}
// 查询管理员
exports.getAdmin = (req, res) => {
    const { id, account } = req.query
    let sqlStr = 'SELECT id, account, privilege FROM admins'
    let params = []

    if (id) {
        sqlStr += ' WHERE id = ?'
        params.push(id)
    } else if (account) {
        sqlStr += ' WHERE account = ?'
        params.push(account)
    }

    db.query(sqlStr, params, (err, result) => {
        if (err) {
            console.log(err)
            return res.send({ status: 0, msg: '查询失败', err })
        }
        res.send({ status: 1, msg: '查询成功', data: result })
    })
}

// 管理员登录
exports.adminLogin = (req, res) => {
    // 应用限流中间件
    adminLoginLimiter(req, res, () => {
        const { account, password } = req.body

        // 对用户输入的密码进行MD5加密
        const encryptedPassword = md5(password)

        const sqlStr = 'SELECT id, account, privilege FROM admins WHERE account = ? AND password = ?'
        db.query(sqlStr, [account, encryptedPassword], (err, result) => {
            if (err) {
                console.log(err)
                return res.send({ status: 0, msg: '登录失败', err })
            }
            if (result.length > 0) {
                res.send({
                    status: 1,
                    msg: '登录成功',
                    data: result[0]
                })
            } else {
                res.send({ status: 0, msg: '账号或密码错误' })
            }
        })
    })
}



// 获取评论列表
exports.getComments = (req, res) => {
    const { loseId } = req.query
    if (!loseId) {
        return res.send({ status: 0, msg: '缺少必要参数loseId' })
    }

    const sqlStr = 'SELECT * FROM comments WHERE loseId = ? ORDER BY id DESC'
    db.query(sqlStr, loseId, (err, result) => {
        if (err) {
            console.log(err)
            return res.send({ status: 0, msg: '获取评论失败', err })
        }
        res.send({
            status: 1,
            msg: '获取评论成功',
            data: result
        })
    })
}

// 添加评论
exports.addComment = (req, res) => {
    const { loseId, openId, content, time, nickName, avatarUrl } = req.body

    // 验证必要字段
    if (!loseId || !openId || !content) {
        return res.send({ status: 0, msg: '缺少必要字段' })
    }

    const sqlStr = 'INSERT INTO comments (loseId, openId, content, time, nickName, avatarUrl) VALUES (?, ?, ?, ?, ?, ?)'
    db.query(sqlStr, [loseId, openId, content, time, nickName, avatarUrl], (err, result) => {
        if (err) {
            console.log(err)
            return res.send({ status: 0, msg: '添加评论失败', err })
        }
        res.send({
            status: 1,
            msg: '添加评论成功',
            data: result
        })
    })
}

// 获取分类列表
exports.getCategories = (req, res) => {
    // 首先获取所有分类
    const sqlStr = 'SELECT * FROM category ORDER BY sort_order ASC'

    db.query(sqlStr, (err, result) => {
        if (err) {
            console.log(err)
            return res.send({
                status: 0,
                msg: '获取分类失败',
                error: err
            })
        }

        // 先创建所有主分类的映射
        const mainCategories = result
            .filter(item => item.parent_id === null)
            .map(item => ({
                id: item.id,
                parent_id: item.parent_id,
                name: item.name,
                icon_url: item.icon_url,
                sort_order: item.sort_order,
                created_at: item.created_at,
                updated_at: item.updated_at,
                children: []
            }))

        // 将子分类添加到对应的主分类中
        result.forEach(item => {
            if (item.parent_id !== null) {
                const parent = mainCategories.find(cat => cat.id === item.parent_id)
                if (parent) {
                    parent.children.push({
                        id: item.id,
                        parent_id: item.parent_id,
                        name: item.name,
                        icon_url: item.icon_url,
                        sort_order: item.sort_order,
                        created_at: item.created_at,
                        updated_at: item.updated_at
                    })
                }
            }
        })

        res.send({
            status: 1,
            msg: '获取分类成功',
            data: mainCategories
        })
    })
}